import Database from "better-sqlite3"
import fs from "fs"
import path from "path"
import { v4 as uuidv4 } from "uuid"

// 确保这个文件只在服务器端运行
if (typeof window !== "undefined") {
  throw new Error("This module should only be used on the server side")
}

// 数据库文件路径
const DB_PATH = path.join(process.cwd(), "votedb.db")

// 初始化数据库
function initializeDatabase() {
  // 检查数据库文件是否存在
  const exists = fs.existsSync(DB_PATH)

  // 创建数据库连接
  const db = new Database(DB_PATH)

  // 如果数据库文件不存在，创建表结构
  if (!exists) {
    console.log("Creating new SQLite database...")

    // 创建投票表
    db.exec(`
      CREATE TABLE polls (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        active INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL
      );
    `)

    // 创建投票选项表
    db.exec(`
      CREATE TABLE poll_options (
        id TEXT PRIMARY KEY,
        poll_id TEXT NOT NULL,
        text TEXT NOT NULL,
        votes INTEGER NOT NULL DEFAULT 0,
        FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
      );
    `)

    // 创建评论表
    db.exec(`
      CREATE TABLE comments (
        id TEXT PRIMARY KEY,
        poll_id TEXT NOT NULL,
        text TEXT NOT NULL,
        created_at TEXT NOT NULL,
        FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
      );
    `)

    // 创建投票记录表
    db.exec(`
      CREATE TABLE vote_records (
        id TEXT PRIMARY KEY,
        poll_id TEXT NOT NULL,
        voter_id TEXT NOT NULL,
        created_at TEXT NOT NULL,
        UNIQUE(poll_id, voter_id),
        FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
      );
    `)
    
    // 创建about表
    db.exec(`
      CREATE TABLE about (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        content TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
    `)

    // 创建用户表
    db.exec(`
      CREATE TABLE users (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'user',
        created_at TEXT NOT NULL
      );
    `)

    // 插入管理员账户
    db.exec(`
      INSERT INTO users (id, name, password, role, created_at)
      VALUES ('admin-user', 'admin', 'admin123', 'admin', datetime('now'));
    `)

    // 启用外键约束
    db.pragma("foreign_keys = ON")

    console.log("Database tables created successfully")
  } else {
    // 检查用户表是否存在，如果不存在则创建
    const userTableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='users'").get()

    if (!userTableExists) {
      console.log("Creating users table...")

      // 创建用户表
      db.exec(`
        CREATE TABLE users (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL UNIQUE,
          password TEXT NOT NULL,
          role TEXT NOT NULL DEFAULT 'user',
          created_at TEXT NOT NULL
        );
      `)

      // 插入管理员账户
      db.exec(`
        INSERT INTO users (id, name, password, role, created_at)
        VALUES ('admin-user', 'admin', 'admin123', 'admin', datetime('now'));
      `)

      console.log("Users table created successfully")
    }
  }

  return db
}

// 获取数据库连接
let dbInstance: any = null

export function getDb() {
  if (!dbInstance) {
    try {
      dbInstance = initializeDatabase()
    } catch (error) {
      console.error("Failed to initialize database:", error)
      throw error
    }
  }
  return dbInstance
}

// 关闭数据库连接
export function closeDb() {
  if (dbInstance) {
    dbInstance.close()
    dbInstance = null
  }
}

// 用户认证函数
export function authenticateUser(username: string, password: string) {
  const db = getDb()

  const user = db
    .prepare(`
    SELECT * FROM users
    WHERE name = ? AND password = ?
  `)
    .get(username, password)

  return user || null
}

// 获取用户信息
export function getUserById(id: string) {
  const db = getDb()

  const user = db
    .prepare(`
    SELECT id, name, role, created_at FROM users
    WHERE id = ?
  `)
    .get(id)

  return user || null
}

// 创建新用户
export function createUser(name: string, password: string, role = "user") {
  const db = getDb()
  const id = uuidv4()
  const now = new Date().toISOString()

  try {
    db.prepare(`
      INSERT INTO users (id, name, password, role, created_at)
      VALUES (?, ?, ?, ?, ?)
    `).run(id, name, password, role, now)

    return getUserById(id)
  } catch (error) {
    console.error("Error creating user:", error)
    return null
  }
}

// 获取关于页面内容
export function getAboutContent() {
  const db = getDb()
  
  // 检查about表是否存在
  const aboutTableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='about'").get()
  
  if (!aboutTableExists) {
    // 如果表不存在，创建表并插入初始数据
    db.exec(`
      CREATE TABLE about (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        content TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
    `)
    
    // 插入初始数据
    const id = uuidv4()
    const now = new Date().toISOString()
    const initialContent = JSON.stringify({
      intro: "本匿名投票系统专为课题《DeepSeek赋能农村中学信息技术课堂教学的路径研究》而设计。系统允许参与者在不暴露个人身份的情况下参与投票和评论，确保每个人都能自由表达自己的真实观点。本研究立足于我国农村中学信息技术教育面临的现实挑战，如设备老化、教师数字素养不足、学生编程能力弱等问题。DeepSeek作为国产AI助手，免费且硬件需求低，支持文本生成、Python调试和知识问答，为资源匮乏的农村学校提供低成本、可推广的教学赋能路径。",
      features: [
        "管理员可以创建和管理投票", 
        "参与者可以匿名投票", 
        "参与者可以查看投票结果", 
        "参与者可以添加匿名评论", 
        "实时统计和展示投票数据",
        "支持多种投票类型和选项设置",
        "提供直观的数据可视化展示",
        "支持DeepSeek辅助教学操作",
        "提供本土化教学案例资源"
      ],
      privacy: "本系统不会收集任何个人身份信息。投票和评论完全匿名，无法追踪到具体的参与者。系统仅使用浏览器本地存储来防止重复投票，不会将任何个人数据发送到服务器。我们严格遵守数据安全与伦理规范，确保DeepSeek应用过程中的数据安全，并对AI教育应用的伦理挑战与负责任创新进行前瞻性思考。",
      instructions: [
        "管理员可以通过\"/admin\"页面创建和管理投票", 
        "参与者可以在首页查看所有活跃的投票", 
        "点击\"参与投票\"按钮进入具体投票页面", 
        "选择一个选项并提交投票", 
        "投票后可以查看结果和添加匿名评论",
        "系统支持多种设备访问，包括手机、平板和电脑",
        "教师可使用DeepSeek辅助备课、授课和评价",
        "学生可参与'DeepSeek应用探索信息社团'活动"
      ]
    })
    
    db.prepare(`
      INSERT INTO about (id, title, description, content, updated_at)
      VALUES (?, ?, ?, ?, ?)
    `).run(id, "关于匿名投票系统", "为DeepSeek赋能农村中学信息技术课堂教学的路径研究而设计", initialContent, now)
  }
  
  // 获取about内容
  return db.prepare("SELECT * FROM about ORDER BY updated_at DESC LIMIT 1").get()
}

// 更新关于页面内容
export function updateAboutContent(title: string, description: string, content: string) {
  const db = getDb()
  const id = uuidv4()
  const now = new Date().toISOString()
  
  try {
    db.prepare(`
      INSERT INTO about (id, title, description, content, updated_at)
      VALUES (?, ?, ?, ?, ?)
    `).run(id, title, description, content, now)
    
    return true
  } catch (error) {
    console.error("Error updating about content:", error)
    return false
  }
}
